import pymysql
import json
import math
from myapp.utils.connection import POOL

# # 连接mysql
# conn = pymysql.Connect(host='localhost', user='root', passwd='123456'
#                        , db='zhifou', port=3306, charset='utf8')

kw = '企业复工'  # 测试用
# pidList = []


def check_generation(key, mid):
    # sql = "SELECT mid FROM {}_findson WHERE pid = 0".format(key)
    # cur = conn.cursor()
    # 4.执行sql命令
    # execute可执行数据库查询select和命令insert，delete，update三种命令(这三种命令需要commit()或rollback())
    # cur.execute(sql)
    # 5.获取数据
    # fetchall遍历execute执行的结果集。取execute执行后放在缓冲区的数据，遍历结果，返回数据。
    # 返回的数据类型是元组类型，每个条数据元素为元组类型:(('第一条数据的字段1的值','第一条数据的字段2的值',...,'第一条数据的字段N的值'),(第二条数据),...,(第N条数据))
    # midData = cur.fetchall()
    # for row in midData:
    #     pidList.append(row[0])
    #     check(key,row[0], 1)
    # print(pidList)
    # cur.close()
    check(key, mid, 1)


# 递归，刷新转发树
def check(key, mid):
    conn = POOL.connection()
    parent_list = ((mid,),)
    son_list = ()
    update_tuple_list = []
    generation = 0
    cur = conn.cursor()
    # .format(key, '+'+mid)
    temp_sql1 = "SELECT mid FROM {}_findson WHERE pid = {}"
    temp_sql2 = "SELECT COUNT(mid) FROM {}_findson WHERE pid = {}"
    temp_sql3 = "UPDATE {}_findson SET generation=%s,sons=%s WHERE mid=%s".format(
        key)
    while True:
        for temp_pid in parent_list:
            pid = temp_pid[0]
            cur.execute(temp_sql2.format(key, '+'+pid))
            sons = cur.fetchall()[0][0]
            update_tuple = (generation, sons, pid)
            update_tuple_list.append(update_tuple)
            # print(update_tuple)
            cur.execute(temp_sql1.format(key, '+'+pid))
            son_list = son_list + cur.fetchall()
            if len(update_tuple_list) > 10000:
                cur.executemany(temp_sql3, update_tuple_list)
                conn.commit()
        if son_list:
            generation += 1
            parent_list = son_list
            son_list = ()
        else:
            break
    if update_tuple_list:
        cur.executemany(temp_sql3, update_tuple_list)
        conn.commit()
    cur.close()


# 找到最大深度
def get_depth(key, mid):
    conn = POOL.connection()
    sql = "SELECT max(generation) FROM {}_findson WHERE rootknot={}".format(
        key, mid)
    cur = conn.cursor()
    cur.execute(sql)
    depth = cur.fetchall()[0][0]
    cur.close()
    print(depth)
    return depth


# 层级分析
def analysis_hierarchy(key, mid):
    conn = POOL.connection()
    hierarchy_num = get_depth(key, mid)
    cur = conn.cursor()
    generation_list = []
    number_list = []

    for generation in range(1, hierarchy_num+1):
        sql = "SELECT COUNT(DISTINCT mid) FROM {}_findson " \
              "WHERE generation = {} AND rootknot = {}".format(
                  key, generation, mid)
        cur.execute(sql)
        generation_list.append(generation)
        number_list.append(cur.fetchall()[0][0])
    cur.close()
    result = [generation_list, number_list]
    print(result)
    return result


def find_parents(key, mid, list):
    # print(list)
    conn = POOL.connection()
    cur = conn.cursor()
    sql = "SELECT pid FROM {}_findson WHERE mid = {}".format(key, mid)
    cur.execute(sql)
    pid = cur.fetchall()[0][0][1:]
    cur.close()
    if pid in list:
        return
    else:
        list.append(pid)
        find_parents(key, pid, list)


def choose_node(key, mid):
    conn = POOL.connection()
    depth = get_depth(key, mid)
    node_list = [mid]
    for i in range(depth, 0, -1):
        # pause = input("暂停")
        print(i, '----------------------------------')
        sum = (1 + i) * i / 2
        chooseNum = math.floor((400-len(node_list))/sum)

        # print(chooseNum)

        cur = conn.cursor()
        sql = "SELECT mid FROM {}_findson " \
              "WHERE rootknot = {} AND generation={} AND sons IS NOT NULL" \
              " ORDER BY sons DESC LIMIT {}".format(
                  key, mid, i, chooseNum*(depth+1-i))
        cur.execute(sql)
        result_set = cur.fetchall()

        # print(result_set)
        counter = 0
        for result in result_set:
            current_mid = result[0]
            if current_mid in node_list:
                continue
            else:
                node_list.append(current_mid)
                counter += 1
                find_parents(key, current_mid, node_list)

                if counter > chooseNum-1:
                    break
        # print(node_list)

    cur.close()
    print(len(node_list))
    return node_list


# 格式转换
def road_loadjson(key, mid):
    conn = POOL.connection()
    # check(key, mid)
    hierarchy_list = analysis_hierarchy(key, mid)
    node_list = choose_node(key, mid)

    json_file = {}
    jsonNode = []
    jsonLink = []
    jsonCata = [{'name': '帖子'}]

    cur = conn.cursor()
    for node in node_list:
        # print(node, '----------------------------------')
        sql = "SELECT f.mid, f.pid, u.name " \
              "FROM {}_findson f, {}_userspider u WHERE f.mid = {} " \
              "AND f.userid = u.uid".format(key, key, node)
        cur.execute(sql)
        tempdata = cur.fetchall()
        # print(tempdata)
        if tempdata:
            data = tempdata[0]
        else:
            continue

        # print(data)
        node_result = {}
        link_result = {}

        node_result['name'] = data[0]
        node_result['username'] = data[2]
        node_result['category'] = '帖子'
        link_result['target'] = data[0]
        if data[1] == '0':
            node_result['symbolSize'] = [10, 10]
            link_result['source'] = data[0]
        else:
            node_result['symbolSize'] = [2, 2]
            link_result['source'] = data[1][1:]

        jsonNode.append(node_result)
        jsonLink.append(link_result)

    json_file['categories'] = jsonCata
    json_file['type'] = "force"
    json_file['nodes'] = jsonNode
    json_file['links'] = jsonLink
    json_file['generation'] = hierarchy_list[0]
    json_file['num'] = hierarchy_list[1]
    json_file['max_generation'] = get_depth(key, mid)
    cur.close()

    conn.close()
    return json_file


# 生成json文件
def writefile(key, mid):
    jsonData = road_loadjson(key, mid)
    jsondatar = json.dumps(jsonData, ensure_ascii=False)
    print(jsondatar)
    f = open(r'getdata.json', 'w+', encoding="utf8")
    # 写数据
    f.write(jsondatar)
    # 关闭文件
    f.close()


if __name__ == '__main__':
    conn = POOL.connection()
    cur = conn.cursor()
    pre_sql = "SELECT mid FROM {}_findson WHERE pid = 0 ORDER BY reposts_count DESC LIMIT 1".format(
        kw)
    cur.execute(pre_sql)
    target_blog = cur.fetchall()[0][0]
    writefile(kw, target_blog)
